<?php

Vendor('PHPExcel.PHPExcel.IOFactory');
Vendor('PHPExcel.PHPExcel.Exception');

define('type_none', 0);
define('type_specification_single', 1); // 单规格
define('type_specification_multi', 2); // 多规格

// 模板行列定义
define('excel_title_row_pdt', 2); // 产品表内容开始行
define('excel_title_row_store', 2); // 门店表内容开始行
define('excel_column_count_none', 14); //无规格列数
define('excel_column_count_single', 14); // 单规格列数
define('excel_column_count_multi', 16); // 多规格列数

define('store_root_name', '门店信息');

class ExcelAction extends Action {
	
	public function importProduct($typeId, $fileName, $importType) {
		$retVal = Session::init();
		if (!$retVal) {
			Output::instance()->resultCode(code_access_denied);
			return;
		}
		$ProductModel = M("module_product");
		
// 		$objPHPExcel = PHPExcel_IOFactory::load(str_replace('.class.php', '.xls', __FILE__));
		$filePath = UPLOAD_BASE_PATH . $fileName;
		try{
			$objPHPExcel = PHPExcel_IOFactory::load($filePath);
		} catch (PHPExcel_Exception $e) {
			Output::instance()->resultCode(code_excel_load_fail, $e->getMessage());
			return;
		}

		$objWorkSheet = $objPHPExcel->getActiveSheet();
		$highestRow = $objWorkSheet->getHighestRow();
		$highestColumn = $objWorkSheet->getHighestColumn();
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
		switch ($importType) {
			case type_specification_multi:
				if ($highestColumnIndex < excel_column_count_multi || $highestRow < excel_title_row_pdt) {
					unlink($filePath);
					Output::instance()->resultCode(code_excel_column_invalid);
					return;
				}
				break;
			case type_specification_single:
				if ($highestColumnIndex < excel_column_count_single || $highestRow < excel_title_row_pdt) {
					unlink($filePath);
					Output::instance()->resultCode(code_excel_column_invalid);
					return;
				}
				break;
			case type_none:
				if ($highestColumnIndex != excel_column_count_none || $highestRow < excel_title_row_pdt) {
					unlink($filePath);
					Output::instance()->resultCode(code_excel_column_invalid);
					return;
				}
				break;
			default:
				Output::instance()->resultCode(code_excel_column_invalid);
				return;
		}
		// 查找规格
		if ($importType != type_none) {
			// 查找第一组规格组
			if ($importType == type_specification_single) {
				$firstColumn = excel_column_count_single - 1;
			} else {
				$firstColumn = excel_column_count_multi - 2;
			}
			$firstGroupName =  $objWorkSheet->getCellByColumnAndRow($firstColumn, 1)->getValue();
			$firstGroupId = $this->findSpecificationGroup($typeId, $firstGroupName);
			if (!$firstGroupId) {
				unlink($filePath);
				return;
			}
			
			if ($importType == type_specification_multi) {
				// 添加第二组规格组
				$secGroupName =  $objWorkSheet->getCellByColumnAndRow(excel_column_count_multi - 1, 1)->getValue();
				$secGroupId = $this->findSpecificationGroup($typeId, $secGroupName);
				if (!$secGroupId) {
					unlink($filePath);
					return;
				}
			}
		}
		if ($importType == type_none) {
			$supportSpecification = 0;
		} else {
			$supportSpecification = 1;
		}
		
		for ($row = excel_title_row_pdt;$row <= $highestRow; $row++) {
			$strs = array();
			for ($col = 0; $col < $highestColumnIndex; $col++) {
				$strs[$col] = $objWorkSheet->getCellByColumnAndRow($col, $row)->getValue();
			}
// 			Log::write('name:' . $strs[0]);
			if ($strs[0] == '') {
				continue;
			}
			
			// 是否添加商品的标志，多规格按照商品条码作为相同商品
			$result = false;
			if ($importType == type_specification_multi) {
				$where["barcode"] = $strs[13];
				$where["deleted"] = 0;
				$pdt = $ProductModel->where($where)->find();
				if ($pdt) {
					$result = $pdt["id"];
				}
			}
			if ($result === false) {
				$data["name"] = strval($strs[0]);
				$data["typeId"] = $typeId;
				$data["brand"] = $strs[1];
				$data["serialNumber"] = $strs[2];
				$data["number"] = $strs[3];
				$data["accessTag"] = strval($strs[5]);
				$data["fixedPrice"] = $strs[6];
				$data["discountedPrice"] = $strs[7];
				$data["title"] = strval($strs[8]);
				$data["description"] = strval($strs[9]);
				$data["recommend"] = strval($strs[10]);
				$data["isRemoved"] = strcmp(strval($strs[11]), '1') == 0 ? false : true;
				$data["inventory"] = strcmp(strval($strs[12]), '1') == 0 ? true : false;
				if ($importType == type_none && $data["inventory"] == true) {
					$data["inventoryCount"] = empty($strs[13]) ? 0 : $strs[13];
				} else {
					$data["inventoryCount"] = 0;
				}
				if ($importType == type_specification_multi) {
					$data["barcode"] = $strs[13];
				}
				$data["supportSpecification"] = $supportSpecification;
				$data["updateTime"] = date("Y-m-d H:i:s");
				
				$result = $ProductModel->add($data);
			}
			
			if ($result === false) {
				unlink($filePath);
				if ($row == excel_title_row_pdt + 1) {
					Output::instance()->resultCode(code_excel_import_fail_all);
				} else {
					Output::instance()->resultCode(code_excel_import_fail);
				}
				Log::write('product import error' . $ProductModel->getLastSql());
				return;
			} else {
				// 设置商品展示标签和用户标签
				$tags = str_replace("，",",", $strs[4]);
				R('Product/setProductTag', array($result, $tags));
				
				$accessTags = str_replace("，", ",", $strs[5]);
				addAccessTags($accessTags);
				
				if ($importType != type_none) {
					// 导入规格
					$resultSpec = $this->setProductSpecification($objWorkSheet, $result, $firstGroupId, $secGroupId, 
						$row, $highestRow, $highestColumnIndex, $importType);
					if (!$resultSpec) {
						unlink($filePath);
						return;
					}
				}
			}
		}
		
		unlink($filePath);
		Output::instance()->resultCode();
	}
	
	
	private function setProductSpecification($objWorkSheet, $pid, $firstGroupId, $secGroupId, $curRow, 
			$highestRow, $highestColumnIndex, $importType) {
		$SpecModel = M("module_product_specification");
		
		switch ($importType) {
			case type_specification_multi:
				$firstSpecName = $objWorkSheet->getCellByColumnAndRow(excel_column_count_multi - 2, $curRow)->getValue();
				for ($secColumn = excel_column_count_multi - 1; $secColumn < $highestColumnIndex; $secColumn++) {
					$secSpecName = $objWorkSheet->getCellByColumnAndRow($secColumn, excel_title_row_pdt)->getValue();
					$inventoryCount = $objWorkSheet->getCellByColumnAndRow($secColumn, $curRow)->getValue();
					Log::write('inv:' . $secColumn . ','. $curRow . ',' . $inventoryCount );
					if (empty($inventoryCount)) {
						$inventoryCount = 0;
					}
					$result = $this->setSpecification($pid, $firstGroupId, $firstSpecName, $secGroupId, $secSpecName, $inventoryCount);
					if (!$result) {
						return false;
					}
				}
				break;
			case type_specification_single:
				for ($firstColumn = excel_column_count_single - 1; $firstColumn < $highestColumnIndex; $firstColumn++) {
					$firstSpecName = $objWorkSheet->getCellByColumnAndRow($firstColumn, excel_title_row_pdt)->getValue();
					$inventoryCount = $objWorkSheet->getCellByColumnAndRow($firstColumn, $curRow)->getValue();
					if (empty($inventoryCount)) {
						$inventoryCount = 0;
					}
					$result = $this->setSpecification($pid, $firstGroupId, $firstSpecName, NULL, '', $inventoryCount);
					if (!$result) {
						return false;
					}
				}
				break;
		}
		return true;
	}
	
	/*
	 * 添加规格组
	 */
	private function findSpecificationGroup($typeId, $groupName) {
		$SpecificationGroupModel = M("module_product_specification_group");
		$where["typeId"] = $typeId;
		$where["groupName"] = $groupName;
		$group = $SpecificationGroupModel->where($where)->find();
		if ($group) {
			return $group["id"];
		} else {
			Log::write($SpecificationGroupModel->getLastSql());
			Output::instance()->resultCode(code_excel_spec_group_name_invalid);
			return false;
		}
	}
	
	/*
	 * 添加规格
	 * secGroupId 规格组id,如果没有第二个规格则该值为NULL
	 * secSpecName 规格名,如果没有第二个规格则该值为NULL
	*/
	private function setSpecification($pid, $firstGroupId, $firstSpecName, $secGroupId, $secSpecName, 
			$inventoryCount) {
		$SpecificationNameModel = M("module_product_specification_name");
		$whereFirstSpec["groupId"] = $firstGroupId;
		$whereFirstSpec["specificationName"] = $firstSpecName;
		$firstSpecId = $SpecificationNameModel->where($whereFirstSpec)->getField("id");
		if (!$firstSpecId) {
			$firstSpecId = $SpecificationNameModel->add($whereFirstSpec);
			if (!$firstSpecId) {
				Output::instance()->resultCode(code_sql_error);
				return false;
			}
		}
		
		if (!empty($secSpecName) && !empty($secGroupId)) {
			$whereSecSpec["groupId"] = $secGroupId;
			$whereSecSpec["specificationName"] = $secSpecName;
			$secSpecId = $SpecificationNameModel->where($whereSecSpec)->getField("id");
			if (!$secSpecId) {
				$secSpecId = $SpecificationNameModel->add($whereSecSpec);
				if (!$secSpecId) {
					Output::instance()->resultCode(code_sql_error);
					return false;
				}
			}
		} else {
			$secSpecId = NULL;
		}
		
		$SpecificationModel = M("module_product_specification");
		$whereInv["pid"] = $pid;
		// id小的作为第一组规格，后台界面要求顺序正确
		if (empty($secGroupId) || $firstGroupId < $secGroupId) {
			$whereInv["firstSpecificId"] = $firstSpecId;
			$whereInv["firstSpecificName"] = $firstSpecName;
			$whereInv["secSpecificId"] = $secSpecId;
			$whereInv["secSpecificName"] = $secSpecName;
		} else {
			$whereInv["firstSpecificId"] = $secSpecId;
			$whereInv["firstSpecificName"] = $secSpecName;
			$whereInv["secSpecificId"] = $firstSpecId;
			$whereInv["secSpecificName"] = $firstSpecName;
		}
		$spec = $SpecificationModel->where($whereInv)->find();
		if ($spec) {
			$data["inventoryCount"] = $inventoryCount;
			$data["updateTime"] = date("Y-m-d H:i:s");
			$result = $SpecificationModel->where($whereInv)->save($data);
		} else {
			$whereInv["inventoryCount"] = $inventoryCount;
			$whereInv["updateTime"] = date("Y-m-d H:i:s");
			$result = $SpecificationModel->add($whereInv);
		}
		
// 		Log::write($SpecificationModel->getLastSql());
		if ($result) {
			return true;
		} else {
			Output::instance()->resultCode(code_sql_error);
			return false;
		}
	}
	/**
	 * 投票导出
	 */
	public function exportTouPiao($actId,$fullName="",$order="a.createTime", $orderBy="desc", $start = 0, $limit = 25){
		
		$expTitle = '投票活动参与人信息' ; //设置文件名
		$fileName = '投票活动参与人信息' . date('Ymd'); //设置文件名
		
		$expCellName  = array(
				array('name','参与人'),
				array('nickname','微信昵称'),
				array('headimgurl','微信头像'),
				array('picurl','作品'),
				array('phone','手机号'),
				array('sex','性别'),
				array('createTime','报名时间'),
				array('votecount','得票数')
					
		);
		$expTableData = R('MiActivity/getTongjiList', array(false,$actId,$fullName,$order,$orderBy,0,1000000));	
		$this->exportExcel($fileName,$expTitle,$expCellName,$expTableData);
	}
	/**
	 * 凯德会员统计数
	 */
	public function exportKdMemberStatic($staType=0,$startTime="",$endTime=""){
		$staTypeName="项目";
		if($staType==1){
			$staTypeName="大区";
		}
		$expTitle = "凯德会员信息(按$staTypeName)统计数据"; //设置文件名
		$fileName = "凯德会员信息(按$staTypeName)统计数据" . date('Ymd'); //设置文件名
		
		$expCellName  = array(
				array('name','类别'),
				array('regNum','新注册会员数'),
				array('num','会员总数')
					
		);
		$expTableData = R('kaideMember/getMemberCountByType', array(false,$staType,$startTime,$endTime,0,1000000));	
		$this->exportExcel($fileName,$expTitle,$expCellName,$expTableData);
	}
	//导出excel核心代码
	public function exportExcel($fileName,$expTitle,$expCellName,$expTableData){
		$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
// 		$fileName = $_SESSION['loginAccount'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定

		$cellNum = count($expCellName);
		$dataNum = count($expTableData);
		
		vendor("PHPExcel.PHPExcel");
		$objPHPExcel = new PHPExcel();
		$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
	
		if($cellNum >0){
			$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
		}else{
			$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[10].'1');//合并单元格
		}
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
		for($i=0;$i<$cellNum;$i++){
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
		}
		// Miscellaneous glyphs, UTF-8
		for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$XS_SJ = $expTableData[$i][$expCellName[$j][0]];
				//用' ' 处理数字为文本,以免excel中数字太大时科学计数法导致错误
				$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), ' '.$XS_SJ);//控制不能显示科学计数数字。
			}
		}
	
		header('pragma:public');
		header("Expires: 0");
		header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");
		header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
		header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		
		exit;
	}
	
}